import Koa from 'koa';
import Router from 'koa-router';
import bodyParser from 'koa-bodyparser';
import { Sequelize, DataTypes, Model } from 'sequelize';

// 创建 Sequelize 实例
const sequelize = new Sequelize(
    'mingcai_db',
    'root',
    'TiRWIsZCwdRXeBxZ',
    {
        host: '95.169.25.181',
        dialect: 'mysql'
    }
);

// 定义模型
class Student extends Model {}
Student.init({
    student_id: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false
    }
}, { sequelize, modelName: 'students' });

class Course extends Model {}
Course.init({
    course_name: {
        type: DataTypes.STRING,
        allowNull: false
    }
}, { sequelize, modelName: 'courses' });

class StudentCourse extends Model {}
StudentCourse.init({
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false
    }
}, { sequelize, modelName: 'StudentCourse' });

// 关联模型
Course.belongsToMany(Student, { through: 'StudentCourse' });
Student.belongsToMany(Course, { through: 'StudentCourse' });

// 创建 Koa 应用和路由
const app = new Koa();
const router = new Router();

// 解析请求体
app.use(bodyParser());

(async () => {
    try {
        await sequelize.authenticate();
        console.log('Connection has been established successfully.');

        await sequelize.sync({ force: true });

        const course_data = [
            { course_name: "Science" },
            { course_name: "Maths" },
            { course_name: "History" }
        ];
        const student_data = [
            { name: "John Baker", courseId: 2 },
            { name: "Max Butler", courseId: 1 },
            { name: "Ryan Fisher", courseId: 3 },
            { name: "Robert Gray", courseId: 2 },
            { name: "Sam Lewis", courseId: 1 }
        ];
        const student_course_data = [
            { studentId: 1, courseId: 1 },
            { studentId: 2, courseId: 1 },
            { studentId: 2, courseId: 3 },
            { studentId: 3, courseId: 2 },
            { studentId: 1, courseId: 2 },
        ];

        await Course.bulkCreate(course_data, { validate: true });
        await Student.bulkCreate(student_data, { validate: true });
        await StudentCourse.bulkCreate(student_course_data, { validate: true });
    } catch (error) {
        console.error('Unable to create table : ', error);
    }
})();

// 定义路由
router.get('/Course/:id', async (ctx) => {
    const { id } = ctx.params;
    ctx.body = await Course.findAll({
        where: { id },
        include: [
            {
                model: Student,
            }
        ],
        attributes: { exclude: ['createdAt', 'updatedAt'] } // 排除默认字段
    });
});

// 定义路由
router.get('/Student/:id', async (ctx) => {
    const { id } = ctx.params;
    ctx.body = await Student.findAll({
        where: { id },
        include: [
            {
                model: Course,
            }
        ],
        attributes: { exclude: ['createdAt', 'updatedAt'] } // 排除默认字段
    });
});

// 使用路由
app.use(router.routes());
app.use(router.allowedMethods());

// 启动服务器
const PORT = 3000;
app.listen(PORT, () => {
    console.log(`Server is running on port ${PORT}`);
});
